﻿IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_QueryGeographyTier]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_QueryGeographyTier]
GO
CREATE PROCEDURE [dbo].[sp_QueryGeographyTier] (
	@ParentKey [uniqueidentifier],
	@Name [nvarchar](max),
	@PostCode [nvarchar](max),
	@TelCode [nvarchar](max),
	@CultureInfo [nvarchar](20)
)
AS
BEGIN
	DECLARE @sqlExp AS NVARCHAR(MAX);
	DECLARE @whereExp AS NVARCHAR(MAX);
	SET @sqlExp = 'SELECT [Key],[ParentKey],[TierValue],[Name],[PostCode],[TelCode],[CountryKey],[CultureInfo],[LastUpdatedStamp] FROM [dbo].[view_GeographyTier]';
	SET @whereExp = '';

	SET @Name = dbo.fn_ReformatStatement(@Name)
	SET @PostCode = dbo.fn_ReformatStatement(@PostCode)
	SET @TelCode = dbo.fn_ReformatStatement(@TelCode)


	IF(@ParentKey IS NOT NULL)
		SET @whereExp = @whereExp + ' [CountryKey] <> [Key] AND [ParentKey]=''' + CONVERT(NVARCHAR(MAX), @ParentKey) + ''' AND ';

	IF(@CultureInfo IS NOT NULL)
		SET @whereExp = @whereExp + ' [CultureInfo]=''' + @CultureInfo + ''' AND ';

	IF(@Name IS NOT NULL)
		SET @whereExp = @whereExp + ' [Name] LIKE ''%' + @Name + '%'' AND ';

	IF(@PostCode IS NOT NULL)
		SET @whereExp = @whereExp + ' [PostCode] LIKE ''%' + @PostCode + '%'' AND ';

	IF(@TelCode IS NOT NULL)
		SET @whereExp = @whereExp + ' [TelCode] LIKE ''%' + @TelCode + '%'' AND';

	IF(@whereExp <> '')
	BEGIN
		SET @whereExp = SUBSTRING(@whereExp, 0, LEN(@whereExp) - 3);
		SET @sqlExp = @sqlExp + ' WHERE ' + @whereExp;
	END

	EXEC sp_executesql @sqlExp;

END

GO


